#!/usr/bin/env python3
# -*- coding:utf-8 -*-

import requests
from bs4 import BeautifulSoup
import zxhr
import pymysql
import time
import send_to_mail

import logging

logging.basicConfig(level=logging.INFO, \
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', \
                    datefmt='%a, %d %b %Y %H:%M:%S', \
                    filename='yyrc.log', \
                    filemode='w')

url_yyrc = 'http://www.yyrc.com/job/?JobType=0&WorkPlace=0&Trade=0&Property=0&JobProperty=0&Degree=0&WorkYears=0&JobTag=0&Weal=0&MonthPay=0&PublishDate=0&Key=外贸&Orderid=0&Styleid=1&PageNo=%d'


def fold_list(list1, list2, list3):
    i = 0
    new_data = []
    while i < len(list1):
        tmp = []
        tmp.append(list1[i])
        tmp.append(list2[i])
        tmp.append(list3[i])
        new_data.append(tmp)
        i += 1

    return new_data


def parser_hrml(html):
    soup = BeautifulSoup(html, 'html.parser')
    title = soup.find_all('li', class_='seaList12')
    company = soup.find_all('li', class_='seaList13')
    date = soup.find_all('li', class_='seaList15')

    t_list = []
    for i in title:
        t_list.append(i.a.text)

    c_list = []
    for i in company:
        c_list.append(i.a.text)

    d_list = []
    for i in date:
        if i.text == '今天':
            timestamp = time.strftime("%Y%m%d")
        else:
            timestamp = ''

        d_list.append(timestamp)

    data = fold_list(t_list, c_list, d_list)
    return data


class yyrcdb:

    def __init__(self):
        self.db = pymysql.connect('localhost', 'spider', 'spider_password', 'spiderdb', charset='utf8')
        self.cursor = self.db.cursor()
        self.str_time = time.strftime("%Y%m%d")
        self.cur_time = time.strftime("%H:%M")

    def check_repeat(self, list):
        sql = 'SELECT id FROM yyrc WHERE title = %s AND company = %s AND date = %s;'

        try:
            self.cursor.execute(sql % (list[0], list[1], list[2]))
            result = self.cursor.fetchall()
        except pymysql.err.InternalError as e:
            logging.warning('[error]  %s' % e)
            result = None

        if result:
            for i in result:
                id = i[0]

                sql_update = "UPDATE yyrc SET is_new = 0 where id = %d;"
                self.cursor.execute(sql_update % id)
                self.db.commit()

            return True
        else:
            return False

    def exist_company(self, data):
        sql = "SELECT company FROM yyrc where company = %s and date = %s;"
        strtime = time.strftime("%Y%m%d")

        company = []
        try:
            self.cursor.execute(sql % (data, strtime))
            result = self.cursor.fetchall()
            for i in result:
                company.append(i)

        except:
            pass

        return len(company) != 0

    def update_company(self):
        query_sql = "SELECT * FROM yyrc WHERE date = %s;"
        update_sql = "UPDATE yyrc SET is_new = 0 where id = %d;"

        try:
            tmp = "'" + self.str_time + "'"
            self.cursor.execute(query_sql % tmp)
            result = self.cursor.fetchall()
            for i in result:
                if i[-1] != self.cur_time:
                    try:
                        self.cursor.execute(update_sql % i[0])
                        self.db.commit()
                    except:
                        self.db.rollback()
        except:
            pass

    def insert(self, data):

        for i in data:
            index = 0
            while index < 3:
                __tmp = "'" + i[index] + "'"
                i[index] = __tmp
                index += 1

            if self.check_repeat(i):
                continue

            if self.exist_company(i[1]):
                sql = 'INSERT INTO yyrc VALUE (NULL, %s, %s, %s, 0, %s);'
            else:
                sql = 'INSERT INTO yyrc VALUES(NULL, %s, %s, %s, 1, %s);'

            try:
                tmp = "'" + self.cur_time + "'"
                self.cursor.execute(sql % (i[0], i[1], i[2], tmp))
                self.db.commit()
            except:
                self.db.rollback()
                logging.warning('insert to database yyrc failed')

    def query(self):

        table = []
        sql = "SELECT id, title, company, date, is_new FROM yyrc WHERE date = %s ORDER BY is_new DESC;"
        strtime = time.strftime("%Y%m%d")

        try:
            self.cursor.execute(sql % strtime)
            result = self.cursor.fetchall()
            for i in result:
                table.append(list(i))
        except:
            logging.warning('[error] query data failed')

        return table

    def __del__(self):
        self.db.close()


if __name__ == '__main__':
    db = yyrcdb()
    i = 1
    while i < 5:
        html = requests.get(url_yyrc % i)
        data = parser_hrml(html.text)

        if not data:
            break

        db.insert(data)
        i += 1

        time.sleep(2)
    db.update_company()

    table_data = db.query()

    flag = False
    for i in table_data:
        if i[-1] == 1:
            flag = True

    table_str = send_to_mail.make_table(['职位名称', '公司名称', '日期', '新条目'], table_data)
    if flag:
        print("sent email")
        send_to_mail.send_email('haokabu@sina.com', table_str, '余姚人才网')
    else:
        logging.info('data update success..but not sent email, program exit -> 0')
